# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
1.获取中移动项目数据库的信息生成相应的对账文件
2.获取数据库信息并对信息进行组合
3.更新数据库字段信息
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import os

from msydwork.config.config_msydloan import cursor, db
from pycacho.cachobase.file_deal import an_save_txt
from pycacho.cachobase.file_deal import get_lines
from pycacho.cachobase.logger import Logger

logger = Logger("mysql_msydloan").get_log()
org_id = '888190900238401'
org_nm = '民盛互联网小额贷款'


def truncate_table():
    logger.info("开始进行数据库数据初始化-清除数据")
    data = get_lines('E:\zenglingwei\data\msydloandb-clear.sql',['TRUNCATE','DELETE'])
    for sql in data:
        print(sql)
        cursor.execute(sql)
        db.commit()


# 中移动项目：对账文件
def get_payInfo(m,n,prov):
    sql = 'select a.brw_ord_no,a.brw_ord_dt,a.process_no,"%s","%s",loan_amt, acp_tm,pick_code,a.mbl_no,model_code,a.app_id,a.opr_id,a.opr_mbl_no,a.dep_id,a.dep_nm,mng_model,prov_stg_day ' \
          'from pkg_notice a,pro_hb_loan b where a.process_no=b.process_no and a.id>="%s" and a.id<="%s" and b.dep_prov_no="%s";' %(org_id,org_nm,m,n,prov)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%m%d%H%M')[0:8]
            except:
                data =str(data)
            row.append(data)
        info.append(row)
    #print(info)
    return(info)


# 中移动项目：退货文件
def get_refundInfo(m,n,file_type):
    sql2 = 'select a.brw_ord_no,a.brw_ord_dt,a.process_no,"%s","%s",b.rpy_mod,b.act_rpy_amt,b.start_time from pkg_notice a,ms_repay_order b where a.process_no=b.process_no and b.id>="%s" and b.id<="%s" and b.status="S";' %(org_id,org_nm,m,n)
    sql4 = 'select a.brw_ord_no,a.brw_ord_dt,"%s","%s",b.act_rpy_amt,b.start_time,b.rpy_mod from pkg_notice a,ms_repay_order b where a.process_no=b.process_no and b.id>="%s" and b.id<="%s" and b.status="S";' % (org_id,org_nm,m, n)
    if file_type == '02':
        cursor.execute(sql2)
    else:
        cursor.execute(sql4)

    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%m%d%H%M')[0:8]
            except:
                data = str(data)
            if data == '2':
                row.append('squared')
            elif data == '3':
                row.append('rejected')
            else:
                row.append(data)
        info.append(row)
    #print(info)
    return (info)


# 批量扣款回盘文件
def get_repayInfo(m,n):
    sql = 'select r.brw_ord_no,l.brw_ord_dt,l.process_no,l.brw_ord_dt,r.rpy_seq,r.repay_amt,r.repay_amt,l.brw_ord_no,start_time,"S","还款成功","ABC","0003","002" from ms_repay_order r,pro_hb_loan l where r.process_no=l.process_no and r.status="N" and r.id>="%s" and r.id<="%s";' %(m,n)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%m%d%H%M')[0:8]
            except:
                data =str(data)
            row.append(data)
        info.append(row)
    #print(info)
    return(info)


# 根据外部借款订单号获取内部生成的案件号
def get_org(brwOrdNo):
    cursor.execute('select process_no from pro_main where ext_serial_number = "%s";' %(brwOrdNo))
    try:
        orgOrdNo = cursor.fetchone()  # 获取一条数据
        return(orgOrdNo[0])
    except:
        print('查无数据！')


# 获取对账文件信息
def get_dat(file):
    with open(file, 'r',encoding= 'utf-8') as fp:
        for line in fp.readlines():
            line = line.lstrip().replace(' ','').replace('\n','')
            data = line.split('|')
            print(data)

def update_msydloan(b_date):
    sql = "update ms_system_date set business_date=%s;"
    cursor.execute(sql, [b_date])
    db.commit()


# 获取数据库信息，生成dat文件   01-办理业务成功订单文件，02-分省提前清贷/退货文件，04-分省提前清贷/退货结算文件，99-批量扣款明细回盘文件
def get_file(file_type,prov,data):
    stg = '888190900238401'
    if file_type == '01':
        file = 'F:\\HBCBF_' + prov + '_' + stg + '_' + file_type + '_' + data + '_001.DAT'
        info = get_payInfo('1', '9',prov)       # file_type = '01'  筛选数据
    elif file_type == '99':
        file = 'F:\\BACK_MPLCASH_888190900238401_DEDUCTION_'+ data + '_001.DAT'
        info = get_repayInfo('1', '10')       # file_type = '01'  筛选数据
    else:
        file = 'F:\\BACK_MPLCASH_888190900238401_DEDUCTION_' + data + '_001.DAT'
        info = get_refundInfo(1,4,file_type)     # file_type = '02' 或04 筛选数据

    if (os.path.exists(file)):
        os.remove(file)  # 删除文件
        print('本地已存在文件：%s，先删除！' % (file))

    TotCnt = len(info)
    TotAmt = 0.00
    for pay in info:
        # file_type01对应5，file_type02对应6, file_type04对应4
        if file_type == '01':
            TotAmt = TotAmt + float(pay[5])
        elif file_type == '02':
            TotAmt = TotAmt + float(pay[6])
        elif file_type == '04':
            TotAmt = TotAmt + float(pay[4])
        elif file_type == '99':
            TotAmt = TotAmt + float(pay[6])
    if file_type == '99':
        total = str(TotCnt) + '|' + '%.2f' % TotAmt + '|' + str(TotCnt) + '|' + '%.2f' % TotAmt + '|0|0.00|'
    else:
        total = str(TotCnt) + '|' + '%.2f' % TotAmt + '|'  # 浮点型两位小数点的字符串
    print(total)
    an_save_txt(file, total)          # 写入文件头

    for pay in info:
        order = '|'.join(pay)
        detail = order + '|'
        print(detail)
        an_save_txt(file, detail)     # 写入文件详情

    an_save_txt(file, '@END@')        # 写入文件尾
    return file


def batch_main(file_type,p,data):
    for i in p:            # 不算尾
        prov = '%02d'%(i)
        get_file(file_type, prov,data)


if __name__ == '__main__':
    #qbmsloan()
    get_file('99', '18','20230303')
    # batch_main('01',[5,18,23],'20200312')
    #get_repayInfo(1, 100)